﻿//---------------------------------------------------------------------
// Copyright (c) 2007 Digerati Technologies, LLC.  All Rights Reserved.
//
// Release under the Microsoft Public License (Ms-PL).
//---------------------------------------------------------------------

using System;
using System.Data.SqlClient;

namespace DigeratiTech.NDiagnostics
{
    /// <summary>
    /// This <see cref="DiagnosticTask"/> verifies that various SQL Server tasks are available.
    /// </summary>
    public class SqlServerTask : DiagnosticTask
    {
        #region Properties...

        /// <summary>
        /// Gets or sets the URL to be validated.
        /// </summary>
        /// <value>The connection string of the database to connect to.</value>
        /// <remarks>The specified connection string must be accessible for this task to succeed.</remarks>
        public string ConnectionString { get; set; }

        /// <summary>
        /// Gets or sets the name of the (optional) table to be validated.
        /// </summary>
        /// <value>The name of the (optional) table to be validated.</value>
        public string TableName { get; set; }

        public string ScalarQuery { get; set; }

        public string ScalarValue { get; set; }

        #endregion

        #region Constructors...

        /// <summary>
        /// Initializes a new instance of the <see cref="UrlTask"/> class.
        /// </summary>
        /// <param name="group">The <see cref="TaskGroup"/> this task belongs to.</param>
        /// <param name="name">The task name.</param>
        /// <param name="connectionString">The connection string of the database to connect to.</param>
        /// <param name="tableName">The name of the (optional) table to be validated.</param>
        /// <param name="scalarQuery">The scalar query.</param>
        /// <param name="scalarValue">The scalar value.</param>
        public SqlServerTask(TaskGroup group, string name, string connectionString, string tableName, string scalarQuery, string scalarValue)
        {
            taskGroup = group;
            Name = name;
            ConnectionString = connectionString;
            TableName = tableName;
            ScalarQuery = scalarQuery;
            ScalarValue = scalarValue;
            Status = TaskStatus.NotRun;
        }

        #endregion

        #region Methods...

        /// <summary>
        /// Executes the task.
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if the task is successful; otherwise, <c>false</c>.
        /// </returns>
        public override bool Execute()
        {
            Status = TaskStatus.Completed;
            Message = string.Empty;

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    // First, let's make sure we can connect to the desired database
                    connection.Open();

                    // Check to ensure a table exists - if specified (this requires "SELECT" permissions
                    if (!string.IsNullOrEmpty(TableName))
                    {
                        string select = "SELECT 1 FROM " + TableName + " WHERE 1 = 2";

                        using (SqlCommand command = new SqlCommand(select, connection))
                        {
                            try
                            {
                                command.ExecuteNonQuery();
                            }
                            catch (SqlException ex)
                            {
                                Status = TaskStatus.Error;
                                Message += ((Message.Length > 0) ? "\n" : "") + ex.Message;
                            }
                        }
                    }

                    // Check to ensure a table exists - if specified (this requires "SELECT" permissions
                    if (!string.IsNullOrEmpty(ScalarQuery))
                    {
                        using (SqlCommand command = new SqlCommand(ScalarQuery, connection))
                        {
                            try
                            {
                                object value = command.ExecuteScalar();

                                if (value == null)
                                {
                                    value = string.Empty;
                                }

                                if (!string.Equals(ScalarValue, value.ToString(), StringComparison.CurrentCultureIgnoreCase))
                                {
                                    Status = TaskStatus.Error;
                                    Message += ((Message.Length > 0) ? "\n" : "") + string.Format("A query result of '{0}' was expected; '{1}' was returned.", ScalarValue, value);
                                }
                            }
                            catch (SqlException ex)
                            {
                                Status = TaskStatus.Error;
                                Message += ((Message.Length > 0) ? "\n" : "") + ex.Message;
                            }
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Status = TaskStatus.Error;
                Message += ((Message.Length > 0) ? "\n" : "") + ex.Message;
            }

            return (Message.Length == 0);
        }

        #endregion
    }
}
